﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Security;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using UwinEducation.Domain.Entities;
using UwinEducation.Domain.IRepositories;
using UwinEducation.Shared.Services;

namespace UwinEducation.Infrastructure.Persistence
{
    public class ApplicationDbContext : DbContext
    {
        private readonly ICurrentUserService _currentUserService;

        public ApplicationDbContext(
            DbContextOptions<ApplicationDbContext> options,
            ICurrentUserService currentUserService
        )
            : base(options)
        {
            _currentUserService = currentUserService;
        }

        public DbSet<User> Users { get; set; }
        public DbSet<Role> Roles { get; set; }
        public DbSet<Permission> Permissions { get; set; }
        public DbSet<UserRole> UserRoles { get; set; }
        public DbSet<RolePermission> RolePermissions { get; set; }
        public DbSet<Classroom> Classrooms { get; set; }
        public DbSet<Course> Courses { get; set; }
        public DbSet<Term> Terms { get; set; }
        public DbSet<Teacher> Teachers { get; set; }
        public DbSet<Student> Students { get; set; }
        public DbSet<StudentPayment> StudentPayments { get; set; }
        public DbSet<Order> Orders { get; set; }
        public DbSet<OrderDetail> OrderDetails { get; set; }
        public DbSet<OrderDiscount> OrderDiscounts { get; set; }
        public DbSet<OrderPayment> OrderPayments { get; set; }
        public DbSet<Appointment> Appointments { get; set; }
        public DbSet<SystemConfig> SystemConfigs { get; set; }
        public DbSet<Description> Descriptions { get; set; }
        

        #region 关系表


        public DbSet<TeacherSchedule> TeacherSchedules { get; set; }
        public DbSet<TeacherScheduleDetails> TeacherScheduleDetails { get; set; }
        public DbSet<TeacherAvailability> TeacherAvailabilities { get; set; }
        public DbSet<TeacherVacation> TeacherVacations { get; set; }

        public DbSet<StudentSchedule> StudentSchedules { get; set; }
        public DbSet<StudentScheduleDetails> StudentScheduleDetails { get; set; }
        #endregion

        public void DropAllForeignKeys()
        {
            // 获取所有外键的删除语句
            var sqlGenerate = @"
                    SELECT
	                    CONCAT( 'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;' ) AS drop_foreign_key_statement 
                    FROM
	                    information_schema.TABLE_CONSTRAINTS 
                    WHERE
	                    TABLE_SCHEMA = 'uwineducation' 
	                    AND CONSTRAINT_TYPE = 'FOREIGN KEY';";

            // 执行查询以获取所有 DROP 语句
            var dropStatements = Database
                .SqlQueryRaw<string>(sqlGenerate)
                .ToList();

            // 逐条执行每个 DROP 语句
            foreach (var statement in dropStatements)
            {
                Database.ExecuteSqlRaw(statement);
            }
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // 遍历所有实体和关系
            foreach (
                var relationship in modelBuilder
                    .Model.GetEntityTypes()
                    .SelectMany(e => e.GetForeignKeys())
            )
            {
                // 禁用外键约束
                relationship.DeleteBehavior = DeleteBehavior.ClientSetNull; // 或其他行为
                relationship.IsRequired = false; // 可选：设置为非必需关系
            }

            // 用户相关索引
            modelBuilder.Entity<User>(entity =>
            {
                entity.HasIndex(e => e.UserName).IsUnique().HasDatabaseName("idx_username");
                entity.HasIndex(e => e.RealName).HasDatabaseName("idx_realname");
                entity.HasIndex(e => e.PhoneNumber).HasDatabaseName("idx_phone");
                entity.HasIndex(e => e.Email).HasDatabaseName("idx_email");
                entity.HasIndex(e => e.CreatedAt).HasDatabaseName("idx_created_at");
            });

            // 学生相关索引
            modelBuilder.Entity<Student>(entity =>
            {
                entity.HasIndex(e => e.StudentNo).HasDatabaseName("idx_student_no");
                entity.HasIndex(e => e.Name).HasDatabaseName("idx_name");
                entity.HasIndex(e => e.School).HasDatabaseName("idx_school");
                entity.HasIndex(e => e.EnrollmentDate).HasDatabaseName("idx_enrollment_date");
            });

            // 订单相关索引
            modelBuilder.Entity<Order>(entity =>
            {
                entity.HasIndex(e => e.StudentId).HasDatabaseName("idx_order_student_id");
                entity.HasIndex(e => e.OrderNumber).HasDatabaseName("idx_order_number");
                entity.HasIndex(e => e.PaymentStatus).HasDatabaseName("idx_order_payment_status");
                entity.HasIndex(e => e.CreatedDate).HasDatabaseName("idx_order_created_date");
            });

            // 订单详情相关索引
            modelBuilder.Entity<OrderDetail>(entity =>
            {
                entity.HasIndex(e => e.OrderId).HasDatabaseName("idx_order_id");
                entity.HasIndex(e => e.TeacherScheduleId).HasDatabaseName("idx_teacher_schedule_id");
            });

            // 订单折扣相关索引
            modelBuilder.Entity<OrderDiscount>(entity =>
            {
                entity.HasIndex(e => e.OrderId).HasDatabaseName("idx_order_id");
            });

            // 描述相关索引
            modelBuilder.Entity<Description>(entity =>
            {
                entity.HasIndex(e => e.Type).HasDatabaseName("idx_type");
                entity.HasIndex(e => e.Title).HasDatabaseName("idx_title");
            });

            // 订单支付相关索引
            modelBuilder.Entity<OrderPayment>(entity =>
            {
                entity.HasIndex(e => e.OrderId).HasDatabaseName("idx_order_id");
                entity.HasIndex(e => e.CreatorId).HasDatabaseName("idx_creator_id");
                entity.HasIndex(e => e.AuditorId).HasDatabaseName("idx_auditor_id");
                entity.HasIndex(e => e.PaymentTime).HasDatabaseName("idx_payment_time");
            });

            // 教师相关索引
            modelBuilder.Entity<Teacher>(entity =>
            {
                entity.HasIndex(e => e.Name).HasDatabaseName("idx_teacher_name");
                entity.HasIndex(e => e.TeacherNo).HasDatabaseName("idx_teacher_no");
            });

            // 教师课表相关索引
            modelBuilder.Entity<TeacherSchedule>(entity =>
            {
                entity.HasIndex(e => e.TeacherId).HasDatabaseName("idx_teacher_id");
                entity.HasIndex(e => e.CourseId).HasDatabaseName("idx_course_id");
                entity.HasIndex(e => e.ClassroomId).HasDatabaseName("idx_classroom_id");
                entity.HasIndex(e => e.GroupId).HasDatabaseName("idx_group_id");
                entity.HasIndex(e => e.StartTime).HasDatabaseName("idx_start_time");
                entity.HasIndex(e => e.EndTime).HasDatabaseName("idx_end_time");
                entity.HasIndex(e => new { e.TeacherId, e.StartTime, e.EndTime }).HasDatabaseName("idx_composite_teacher_date");
            });

            // 教师课表明细相关索引
            modelBuilder.Entity<TeacherScheduleDetails>(entity =>
            {
                entity.HasIndex(e => e.TeacherScheduleId).HasDatabaseName("idx_teacher_schedule_id");
                entity.HasIndex(e => e.StartTime).HasDatabaseName("idx_start_time");
                entity.HasIndex(e => e.EndTime).HasDatabaseName("idx_end_time");
                entity.HasIndex(e => new { e.TeacherScheduleId, e.StartTime, e.EndTime }).HasDatabaseName("idx_composite_schedule_time");
            });

            // 学生课表相关索引
            modelBuilder.Entity<StudentSchedule>(entity =>
            {
                entity.HasIndex(e => e.StudentId).HasDatabaseName("idx_student_id");
                entity.HasIndex(e => e.TeacherScheduleId).HasDatabaseName("idx_teacher_schedule_id");
                entity.HasIndex(e => e.TeacherScheduleDetailsId).HasDatabaseName("idx_teacher_schedule_details_id");
                entity.HasIndex(e => e.Status).HasDatabaseName("idx_status");
                entity.HasIndex(e => new { e.StudentId, e.TeacherScheduleId }).HasDatabaseName("idx_composite_student_schedule");
                entity.HasIndex(e => new { e.StudentId, e.TeacherScheduleDetailsId }).HasDatabaseName("idx_composite_student_details");
            });

            // 角色权限相关索引
            modelBuilder.Entity<UserRole>(entity =>
            {
                entity.HasIndex(e => e.UserId).HasDatabaseName("idx_user_id");
                entity.HasIndex(e => e.RoleId).HasDatabaseName("idx_role_id");
                entity.HasIndex(e => new { e.UserId, e.RoleId }).IsUnique().HasDatabaseName("idx_unique_user_role");
            });

            modelBuilder.Entity<RolePermission>(entity =>
            {
                entity.HasIndex(e => e.RoleId).HasDatabaseName("idx_role_id");
                entity.HasIndex(e => e.PermissionId).HasDatabaseName("idx_permission_id");
                entity.HasIndex(e => new { e.RoleId, e.PermissionId }).IsUnique().HasDatabaseName("idx_unique_role_permission");
            });

            // 为所有实现了ISoftDelete的实体添加全局过滤器
            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
            {
                if (typeof(ISoftDelete).IsAssignableFrom(entityType.ClrType))
                {
                    var parameter = Expression.Parameter(entityType.ClrType, "e");
                    var property = Expression.Property(parameter, nameof(ISoftDelete.IsDeleted));
                    var falseConstant = Expression.Constant(false);
                    var lambda = Expression.Lambda(
                        Expression.Equal(property, falseConstant),
                        parameter
                    );

                    modelBuilder.Entity(entityType.ClrType).HasQueryFilter(lambda);
                }
            }
        }

        public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
        {
            foreach (var entry in ChangeTracker.Entries<EntityBase>())
            {
                switch (entry.State)
                {
                    case EntityState.Added:
                        entry.Entity.CreatedAt = DateTime.Parse(
                            DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                        );
                        entry.Entity.CreatedBy = _currentUserService.UserId;
                        //entry.Entity.UpdatedAt = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Unspecified);
                        break;
                    case EntityState.Modified:
                        entry.Entity.UpdatedAt = DateTime.Parse(
                            DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                        );
                        entry.Entity.UpdatedBy = _currentUserService.UserId;
                        //entry.Entity.UpdatedAt = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Unspecified);
                        break;
                }
            }

            return base.SaveChangesAsync(cancellationToken);
        }


    }
}


